package com.yulian.web_api.report;

import java.util.*;

import com.healthmarketscience.sqlbuilder.Conditions;
import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;
import com.yulian.util.DataUtils;

import com.yulian.util.TimeUtils;
import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.auth.User;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

/**
 * 该类位于业务逻辑层，主要实现商品毛利及提成管理报表数据查询，新增数据，更新数据，删除数据、导出、打印数据的业务逻辑，为MainVerticle中的REST接口提供服务
 *
 * @author
 */
public class GoodsGrossProfitCommissionManager {
    final InternalLogger logger = Log4JLoggerFactory.getInstance(GoodsGrossProfitCommissionManager.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();

    /**
     * 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("big_area_id_list", "site_id_list", "warehouse_id_list", "goods_gather_area_id_list", "supplier_id_list", "supplier_pickup_goods_inspect_accept_code_list", "page_size", "page_index");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        //从bodyJson中获取数据
//        String startTimeParam = bodyJSObj.getString("startTimeParam");
//        String endTimeParam = bodyJSObj.getString("endTimeParam");
        String big_area_id_list = bodyJSObj.getString("big_area_id_list");
        String site_id_list = bodyJSObj.getString("site_id_list");
        String warehouse_id_list = bodyJSObj.getString("warehouse_id_list");
        String goods_gather_area_id_list = bodyJSObj.getString("goods_gather_area_id_list");
        String supplier_id_list = bodyJSObj.getString("supplier_id_list");
        String supplier_pickup_goods_inspect_accept_code_list = bodyJSObj.getString("supplier_pickup_goods_inspect_accept_code_list");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //数据是否为空校验
        if (DataUtils.isEmpty( big_area_id_list, site_id_list, goods_gather_area_id_list, warehouse_id_list, supplier_id_list, page_size, page_index)) {
            promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        }
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();
        String condition ="";
        if (!big_area_id_list.equals("[]")){
            String str = big_area_id_list.substring(1,big_area_id_list.length()-1);
            condition=condition+"and w.big_area_id in ("+str+") ";
        }
        if (!site_id_list.equals("[]")){
            String str = site_id_list.substring(1,site_id_list.length()-1);
            condition=condition+"and w.site_id in ("+str+") ";
        }
        if (!warehouse_id_list.equals("[]")){
            String str = warehouse_id_list.substring(1,warehouse_id_list.length()-1);
            condition=condition+"spgia.warehouse_id in ("+str+") ";
        }
        if (!goods_gather_area_id_list.equals("[]")){
            String str = goods_gather_area_id_list.substring(1,goods_gather_area_id_list.length()-1);
            condition=condition+"spgia.goods_gather_area_id in ("+str+") ";
        }
        if (!supplier_id_list.equals("[]")){
            String str = supplier_id_list.substring(1,supplier_id_list.length()-1);
            condition=condition+"spgia.supplier_id in ("+str+") ";
        }
        if (!supplier_pickup_goods_inspect_accept_code_list.equals("[]")){
            String str = supplier_pickup_goods_inspect_accept_code_list.substring(1,supplier_pickup_goods_inspect_accept_code_list.length()-1);
            condition=condition+"spgia.supplier_pickup_goods_inspect_accept_code in ("+str+") ";
        }


        String finalCondition = condition;
        this.commonRepository.getCon(client).onSuccess(conn -> {
                //resJSArray中就是获取到符合条件的数据
                String count_sql = "select count(*) as total " +
                        "from supplier_pickup_goods_inspect_accept spgia left join warehouse w on w.id=spgia.warehouse_id " +
                        "where spgia.is_delete=0  "+ finalCondition;
                Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_supplier_pickup_goods_inspect_accept");
                String count_sql2 = "select count(*) as total " +
                        "from sales_supplier_pickup_goods_inspect_accept spgia left join warehouse w on w.id=spgia.warehouse_id " +
                        "where spgia.is_delete=0 "+ finalCondition;
                Future<Integer> getTotalFuture2 = this.commonRepository.getRecordsCount(conn, count_sql2, "get_sales_supplier_pickup_goods_inspect_accept");
                String sql ="select spgia.warehouse_id, " +
                        "       spgia.id, " +
                        "       w.warehouse_name, w.big_area_id, w.site_id , " +
                        "       (select s.site_name from site s where s.is_delete=0 and s.id=w.site_id) as site_name, " +
                        "       (select s.big_area_name from big_area s where s.is_delete=0 and s.id=w.big_area_id) as big_area_name, " +
                        "    spgia.supplier_id, " +
                        "    (select si.supplier_name " +
                        "     from supplier_info si " +
                        "     where si.is_delete=0 " +
                        "     and si.id=spgia.supplier_id)  as supplier_name, " +
                        "       spgia.arrival_date,spgia.supplier_pickup_goods_inspect_accept_code, " +
                        "       spgia.goods_gather_area_id, " +
                        "       (select gga.goods_gather_area_name from goods_gather_area gga where gga.is_delete=0 and gga.id=spgia.goods_gather_area_id ) as goods_gather_area_name " +
                        "from supplier_pickup_goods_inspect_accept spgia left join warehouse w on w.id=spgia.warehouse_id " +
                        "where spgia.is_delete=0  "+ finalCondition+" order by spgia.id desc limit "+query_limit+" offset "+query_offset+" ";
                Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecords");
            String sql2 ="select spgia.warehouse_id, " +
                    "       spgia.id, " +
                    "       w.warehouse_name, w.big_area_id, w.site_id , " +
                    "       (select s.site_name from site s where s.is_delete=0 and s.id=w.site_id) as site_name, " +
                    "       (select s.big_area_name from big_area s where s.is_delete=0 and s.id=w.big_area_id) as big_area_name, " +
                    "    spgia.supplier_id, " +
                    "    (select si.supplier_name " +
                    "     from supplier_info si " +
                    "     where si.is_delete=0 " +
                    "     and si.id=spgia.supplier_id)  as supplier_name, " +
                    "       spgia.arrival_date,spgia.supplier_pickup_goods_inspect_accept_code, " +
                    "       spgia.goods_gather_area_id, " +
                    "       (select gga.goods_gather_area_name from goods_gather_area gga where gga.is_delete=0 and gga.id=spgia.goods_gather_area_id ) as goods_gather_area_name " +
                    "from sales_supplier_pickup_goods_inspect_accept spgia left join warehouse w on w.id=spgia.warehouse_id " +
                    "where spgia.is_delete=0  "+ finalCondition+" order by spgia.id desc limit "+query_limit+" offset "+query_offset+" ";
                Future<JsonArray> future2 =  this.commonRepository.queryJsonArray(conn, sql2, "getRecords2");
                CompositeFuture.join(Arrays.asList(getTotalFuture, future,getTotalFuture2,future2)).onComplete(ar2 -> {
                    if (ar2.succeeded()) {
                        int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                        int total_records1 = Integer.valueOf(ar2.result().list().get(2).toString());
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                        JsonArray recordsJSArray2=(JsonArray)ar2.result().list().get(3);
                        JsonArray allArray = recordsJSArray.addAll(recordsJSArray2);
                        dataJSObj.put("total", total_records+total_records1).put("records",allArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    } else {
                        logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                        promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getGoodsGrossProfitCommissionRecords", "获取商品毛利及提成管理用失败"));
                    }
                });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsGrossProfitCommissionRecords", "数据库连接失败")));

        return promise.future();
    }




    /**
     * 创建查询符合搜索条件记录的sql语句
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"), new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "),
        new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
        new CustomSql("goods_gather_area_id"),new CustomSql("(select gga.goods_gather_area_name from goods_gather_area gga where gga.is_delete=0 and gga.id=goods_gather_area_id ) as goods_gather_area_name "),
        new CustomSql("supplier_id"), new CustomSql("(select si.supplier_name from supplier_info si where si.is_delete=0 and si.id=supplier_id ) as supplier_name "), new CustomSql("arrival_date"), new CustomSql("supplier_pickup_goods_inspect_accept_code"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("supplier_pickup_goods_inspect_accept");
//        sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(startTimeParam, "yyyy-MM-dd HH:mm:ss")));
//        sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(endTimeParam, "yyyy-MM-dd HH:mm:ss")));
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if ("start_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("arrival_date"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                } else if ("end_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("arrival_date"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                }
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where InCondtion子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 创建查询符合搜索条件记录的sql语句
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql2(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"), new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "),
        new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
        new CustomSql("goods_gather_area_id"),new CustomSql("(select gga.goods_gather_area_name from goods_gather_area gga where gga.is_delete=0 and gga.id=goods_gather_area_id ) as goods_gather_area_name "),
        new CustomSql("supplier_id"), new CustomSql("(select si.supplier_name from supplier_info si where si.is_delete=0 and si.id=supplier_id ) as supplier_name "), new CustomSql("arrival_date"), new CustomSql("supplier_pickup_goods_inspect_accept_code"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("sales_supplier_pickup_goods_inspect_accept");
//        sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(startTimeParam, "yyyy-MM-dd HH:mm:ss")));
//        sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(endTimeParam, "yyyy-MM-dd HH:mm:ss")));
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if ("start_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("arrival_date"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                } else if ("end_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("arrival_date"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                }
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where InCondtion子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }
}